package tools
import (
    "gorm.io/gorm"
    "errors"
)
//用户表
type User struct {
	ID       int64  `gorm:"column:id"` // 主键
	Username string `gorm:"column:name"`
	Password string `gorm:"column:password"`
	Email    string `gorm:"column:email"`

}
func (User) TableName() string {
	return "user"
}
//学生表
type Student struct {
    ID             int64   `gorm:"column:id"`
    StudentID      string  `gorm:"column:student_id"`
    Name           string  `gorm:"column:name"`
    Gender         string  `gorm:"column:gender"`
    EnrollmentYear string   `gorm:"column:enrollment_year"`
    MajorID        string  `gorm:"column:majorID"`
    ClassID        string  `gorm:"column:classID"`
    ParentPhone    string  `gorm:"column:parent_phone"`
    FinalAvgScore  float64 `gorm:"column:final_avg_score"`
    Status         string  `gorm:"column:status"`
}
func (Student) TableName() string {
	return "student"
}
//班级表
type Class struct {
    ClassID      string `gorm:"column:ClassID"`
    StudentCount int  `gorm:"column:StudentCount"`
    TeacherID    string `gorm:"column:TeacherID"`
    ClassName    string `gorm:"column:ClassName"`
    MajorID      int    `gorm:"column:MajorID"`
}

func (Class) TableName() string {
    return "class"
}
//专业表
type Major struct {
    MajorID int    `gorm:"column:MajorID"`
    Name    string `gorm:"column:Name"`
}
func (Major) TableName() string {
    return "major"
}
//老师表
type Teacher struct {
    TeacherID int `gorm:"cloumn:TeacherID ;"`
    Name string `gorm:"cloumn: Name"`
}
func (Teacher) TableName() string {
    return "teacher"
}
//更好的学生表
type StudentWithDetails struct {
    Student
    MajorName string
    ClassName string
}
//登录查询
//注册插入
func Register(db *gorm.DB, user User) error {
    return db.Create(&user).Error
}
// 验证ClassID是否存在
func ClassExists(db *gorm.DB, classID string) bool {
    var count int64
    db.Model(&Class{}).Where("ClassID = ?", classID).Count(&count)
    return count > 0
}

// 验证MajorID是否存在
func MajorExists(db *gorm.DB, majorID string) bool {
    var count int64
    db.Model(&Major{}).Where("MajorID = ?", majorID).Count(&count)
    return count > 0
}
//查询学生信息
//单个查询


// 全部查询
func FindStudents(db *gorm.DB, page int, pageSize int) ([]StudentWithDetails, error) {
    var studentsWithDetails []StudentWithDetails
    offset := (page - 1) * pageSize
    err := db.Table("student").
        Select("student.*, major.Name as MajorName, class.ClassName").
        Joins("JOIN major ON student.majorID = major.MajorID").
        Joins("JOIN class ON student.classID = class.ClassID").
        Limit(pageSize).
        Offset(offset).
        Scan(&studentsWithDetails).Error
    return studentsWithDetails, err
}

//添加学生信息
func AddStudent(db *gorm.DB, student Student) error {
    // 检查学生ID是否已存在
    var existingStudent Student
    if db.Where("student_id = ?", student.StudentID).First(&existingStudent).Error == nil {
        return errors.New("学生ID已存在")
    }
    
    // 验证ClassID是否存在
    if !ClassExists(db, student.ClassID) {
        return errors.New("班级ID不存在")
    }
    
    // 验证MajorID是否存在
    if !MajorExists(db, student.MajorID) {
        return errors.New("专业ID不存在")
    }

    // 添加学生
    return db.Create(&student).Error
}
//修改学生信息



//删除学生信息
// 删除学生信息
func DeleteStudent(db *gorm.DB, studentID string) error {
    var student Student
    result := db.Where("student_id = ?", studentID).Delete(&student)
    if result.Error != nil {
        return result.Error
    }
    if result.RowsAffected == 0 {
        return errors.New("学生ID不存在")
    }
    return nil
}

// 删除教师信息
func DeleteTeacher(db *gorm.DB, teacherID int) error {
    var teacher Teacher
    result := db.Where("TeacherID = ?", teacherID).Delete(&teacher)
    if result.Error != nil {
        return result.Error
    }
    if result.RowsAffected == 0 {
        return errors.New("教师ID不存在")
    }
    return nil
}

// 删除班级信息
func DeleteClass(db *gorm.DB, classID string) error {
    var class Class
    result := db.Where("ClassID = ?", classID).Delete(&class)
    if result.Error != nil {
        return result.Error
    }
    if result.RowsAffected == 0 {
        return errors.New("班级ID不存在")
    }
    return nil
}

// 删除专业信息
func DeleteMajor(db *gorm.DB, majorID int) error {
    var major Major
    result := db.Where("MajorID = ?", majorID).Delete(&major)
    if result.Error != nil {
        return result.Error
    }
    if result.RowsAffected == 0 {
        return errors.New("专业ID不存在")
    }
    return nil
}


// 查询班级信息
func FindClasses(db *gorm.DB, page int, pageSize int) ([]Class, error) {
    var classes []Class
    offset := (page - 1) * pageSize
    err := db.Limit(pageSize).Offset(offset).Find(&classes).Error
    return classes, err
}

// 添加班级信息
func AddClass(db *gorm.DB, class Class) error {
    // 检查班级ID是否已存在
    var existingClass Class
    if db.Where("ClassID = ?", class.ClassID).First(&existingClass).Error == nil {
        return errors.New("班级ID已存在")
    }

    // 添加班级
    return db.Create(&class).Error
}

// 查询专业信息
func FindMajors(db *gorm.DB, page int, pageSize int) ([]Major, error) {
    var majors []Major
    offset := (page - 1) * pageSize
    err := db.Limit(pageSize).Offset(offset).Find(&majors).Error
    return majors, err
}

// 添加专业信息
func AddMajor(db *gorm.DB, major Major) error {
    // 检查专业ID是否已存在
    var existingMajor Major
    if db.Where("MajorID = ?", major.MajorID).First(&existingMajor).Error == nil {
        return errors.New("专业ID已存在")
    }

    // 添加专业
    return db.Create(&major).Error
}

// 查询教师信息
func FindTeachers(db *gorm.DB, page int, pageSize int) ([]Teacher, error) {
    var teachers []Teacher
    offset := (page - 1) * pageSize
    err := db.Limit(pageSize).Offset(offset).Find(&teachers).Error
    return teachers, err
}

// 添加教师信息
func AddTeacher(db *gorm.DB, teacher Teacher) error {
    // 检查教师ID是否已存在
    var count int64
    if err := db.Raw("SELECT COUNT(*) FROM teacher WHERE TeacherID = ?", teacher.TeacherID).Scan(&count).Error; err != nil {
        return err
    }
    
    if count > 0 {
        return errors.New("教师ID已存在")
    }
    
    // 使用原生SQL插入教师数据
    result := db.Exec(
        "INSERT INTO teacher (TeacherID,name) VALUES (?,?)",
        teacher.TeacherID, teacher.Name,
    )
    
    return result.Error
}
